from datetime import date
from openpyxl import Workbook
from openpyxl.chart import (
    LineChart,  # 线图
    Reference,
)
from openpyxl.chart.axis import DateAxis  # 时间轴

# 创建工作表
wb = Workbook()
ws = wb.active

# 创建数据
rows = [
    ['Date', 'Batch 1', 'Batch 2', 'Batch 3'],
    [date(2015, 9, 1), 40, 30, 25],
    [date(2015, 9, 2), 40, 25, 30],
    [date(2015, 9, 3), 50, 30, 45],
    [date(2015, 9, 4), 30, 25, 40],
    [date(2015, 9, 5), 25, 35, 30],
    [date(2015, 9, 6), 20, 40, 35],
]

# 向工作表中添加数据
for row in rows:
    ws.append(row)

# Chart with date axis
c2 = LineChart()

# 地区第2-4列,第1-7行数据
data = Reference(ws, min_col=2, min_row=1, max_col=4, max_row=7)

c2.title = "时间轴"
c2.style = 12
c2.y_axis.title = "数值"
c2.y_axis.crossAx = 500
c2.x_axis = DateAxis(crossAx=100)
c2.x_axis.number_format = 'mm-dd'  # 日期格式化
c2.x_axis.majorTimeUnit = "days"
c2.x_axis.title = "日期"

# 添加数据
c2.add_data(data, titles_from_data=True)
# 添加日期
dates = Reference(ws, min_col=1, min_row=2, max_row=7)
# 设置分类
c2.set_categories(dates)
# 将图表添加到工作表指定位置
ws.add_chart(c2, "A10")
wb.save("line2.xlsx")
